//
//  ZHFMDB.m
//  XMLYFM
//
//  Created by dllo on 15/12/28.
//  Copyright © 2015年 zzl. All rights reserved.
//

#import "ZHFMDB.h"

@implementation ZHFMDB
+ (instancetype)shareFMDB{
    
    static ZHFMDB *fmdb = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        
        fmdb = [[ZHFMDB alloc]init];
    });
    return fmdb;
}


- (void)openFMDB{
    // 获得Documents目录路径
    
    NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    
    // 文件路径
    
    NSString *filePath = [documentsPath stringByAppendingPathComponent:@"ZHFMDBase.sqlite"];
    
    NSLog(@"%@",documentsPath);
    
    self.db = [FMDatabase databaseWithPath:filePath];
    
}

- (void)openFMDBAndCreateTable{
    // 获得Documents目录路径
    
    NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    
    // 文件路径
    
    NSString *filePath = [documentsPath stringByAppendingPathComponent:@"ZHFMDataBase.sqlite"];
    NSLog(@"filePath = %@",filePath);
    self.db = [FMDatabase databaseWithPath:filePath];

    if ([self.db open]) {
        NSLog(@"ZHFMDB  open");
        //创建 专辑表 albums
        int result = [self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS albums (albumId INTEGER PRIMARY KEY , albumName TEXT NOT NULL, albumPic TEXT NOT NULL, albumIntro TEXT NOT NULL, albumTracks TEXT NOT NULL, myTitle TEXT NOT NULL);"];
        if (result) {
            NSLog(@"创建album表成功");
        }else{
             NSLog(@"创建album表失败");
        }
        
        //声音表
        int result1=[self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS tracks (trackId INTEGER PRIMARY KEY , trackName TEXT NOT NULL, trackPic TEXT NOT NULL, trackIntro TEXT NOT NULL, trackPlay TEXT NOT NULL, trackLike INTEGER NOT NULL);"];
        
        if (result1) {
            NSLog(@"创tracks表成功");
        }else{
            NSLog(@"创tracks表失败");
        }

        
    }
    [self.db close];
}

#pragma mark - 删除Albums表
- (void)dropTableAlbums{
   
    [self.db open];
    [self.db executeUpdate:@"DROP TABLE IF EXISTS albums"];
    //创建 专辑表 albums
    int result = [self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS albums (albumId INTEGER PRIMARY KEY , albumName TEXT NOT NULL, albumPic TEXT NOT NULL, albumIntro TEXT NOT NULL, albumTracks TEXT NOT NULL, myTitle TEXT NOT NULL );"];
    if (result) {
        NSLog(@"创建album表成功");
    }else{
        NSLog(@"创建album表失败");
    }
    [self.db close];
}
#pragma mark - 删除Tracks表
- (void)dropTableTracks{
    
    [self.db open];
    [self.db executeUpdate:@"DROP TABLE IF EXISTS tracks"];
    //声音表
    int result1=[self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS tracks (trackId TEXT PRIMARY KEY , trackName TEXT NOT NULL, trackPic TEXT NOT NULL, trackIntro TEXT NOT NULL, trackPlay TEXT NOT NULL, trackLike INTEGER NOT NULL);"];
    
    if (result1) {
        NSLog(@"创tracks表成功");
    }else{
        NSLog(@"创tracks表失败");
    }
    [self.db close];
}
#pragma mark - 插入数据
-(void)insertAlbumData:(ZTFavorModel *)album
{
    [self.db open];
    //albumId INTEGER PRIMARY KEY , albumName TEXT NOT NULL, albumPic TEXT NOT NULL, albumIntro TEXT NOT NULL, albumTracks TEXT NOT NULL, myTitle TEXT NOT NULL
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO albums(albumId, albumName, albumPic, albumIntro, albumTracks, myTitle) VALUES('%@','%@','%@', '%@', '%@', '%@')",album.albumId, album.title, album.coverMiddle,album.intro, album.tracks,album.albumTitle];
    
    int result = [self.db executeUpdate:sql];
    if (result) {
        
        NSLog(@"插入AlbumData成功");
        
    }else
    {
        NSLog(@"插入AlbumData失败");
        
    }
    [self.db close];

}
-(void)insertTrackData:(ZTAlbumModel *)track
{
    [self.db open];
    
    //如果用stringWithFormat时,sql语句中可以用'%@'
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO tracks(trackId, trackName, trackPic, trackIntro, trackPlay, trackLike) VALUES('%ld','%@','%@', '%@', '%ld', '%ld')", track.trackId, track.title, track.coverLarge,track.nickname, track.playtimes,track.likes];
    
    int result = [self.db executeUpdate:sql];
    if (result) {
        
        NSLog(@"插入TrackData成功");
        
    }else
    {
        NSLog(@"插入TrackData失败 ");
        
    }
    [self.db close];
}


#pragma mark 查询数据
-(NSMutableArray *)selectAlbumData
{
    [self.db open];
    
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from albums"];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        //albumId INTEGER PRIMARY KEY , albumName TEXT NOT NULL, albumPic TEXT NOT NULL, albumIntro TEXT NOT NULL, albumTracks TEXT NOT NULL, myTitle TEXT NOT NULL
        
        NSString *idNum = [resultSet objectForColumnName:@"albumId"];
        NSString *title = [resultSet objectForColumnName:@"albumName"];
        NSString *pic = [resultSet objectForColumnName:@"albumPic"];
        NSString *info = [resultSet objectForColumnName:@"albumIntro"];
        NSString *trackNum = [resultSet objectForColumnName:@"albumTracks"];
        NSString *myTitle = [resultSet objectForColumnName:@"myTitle"];
        
        ZTFavorModel *model = [[ZTFavorModel alloc] init];
        model.title = title;
        model.intro = info;
        model.coverMiddle = pic;
        model.tracks = trackNum;
        model.albumTitle = myTitle;
        model.albumId = idNum;
        
        [array addObject:model];
        
        //        NSLog(@"%@", movie);
    }
    
    [self.db close];
    return array;
    
}
-(NSMutableArray *)selectTrackData
{
    [self.db open];
    
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from tracks"];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        NSInteger idNum = [resultSet intForColumn:@"trackId"];
        NSString *title = [resultSet objectForColumnName:@"trackName"];
        NSString *pic = [resultSet objectForColumnName:@"trackPic"];
        NSString *info = [resultSet objectForColumnName:@"trackIntro"];
        
        
        ZTAlbumModel *model = [[ZTAlbumModel alloc] init];
        model.title = title;
        model.trackId = idNum;
        model.coverLarge = pic;
        model.personInfo = info;
        
        
        [array addObject:model];
        
        //        NSLog(@"%@", movie);
    }
    
    [self.db close];
    return array;
    
}

-(NSMutableArray *)selectTrackImageData
{
    [self.db open];
    
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from tracks"];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        NSString *pic = [resultSet objectForColumnName:@"trackPic"];
        
        
        ZTAlbumModel *model = [[ZTAlbumModel alloc] init];
        model.coverSmall = pic;
        [array addObject:model.coverSmall];
        
    }
    
    [self.db close];
    return array;
    
}

-(NSMutableArray *)selectTrackIDData
{
    [self.db open];
    
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from tracks"];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        //    (trackId INTEGER PRIMARY KEY , trackName TEXT NOT NULL, trackPic TEXT NOT NULL, trackIntro TEXT NOT NULL, trackPlay TEXT NOT NULL, trackLike TEXT NOT NULL)
        NSInteger idNum = [resultSet intForColumn:@"trackId"];
        
        ZTAlbumModel *model = [[ZTAlbumModel alloc] init];
        model.trackId = idNum;
        
        [array addObject:model];
        
        //        NSLog(@"%@", movie);
    }
    
    [self.db close];
    return array;
    
}

-(NSMutableArray *)selectTrackNameData
{
    [self.db open];
    
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from tracks"];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        //    (trackId INTEGER PRIMARY KEY , trackName TEXT NOT NULL, trackPic TEXT NOT NULL, trackIntro TEXT NOT NULL, trackPlay TEXT NOT NULL, trackLike TEXT NOT NULL)
        NSString *title = [resultSet objectForColumnName:@"trackName"];
        
        ZTAlbumModel *model = [[ZTAlbumModel alloc] init];
        model.title = title;
        //
        
        [array addObject:model.title];
        
        //        NSLog(@"%@", movie);
    }
    
    [self.db close];
    return array;
    
}


#pragma mark 查询数据
-(NSMutableArray *)selectAlbumIdData
{
    [self.db open];
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from albums"];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        
        NSString *idNum = [resultSet objectForColumnName:@"albumId"];
//        NSString *title = [resultSet objectForColumnName:@"albumName"];
//        NSString *pic = [resultSet objectForColumnName:@"albumPic"];
//        NSString *info = [resultSet objectForColumnName:@"albumIntro"];
//        NSString *trackNum = [resultSet objectForColumnName:@"albumTracks"];
//        NSString *myTitle = [resultSet objectForColumnName:@"myTitle"];
        
//        ZTFavorModel *model = [[ZTFavorModel alloc] init];
//        model.title = title;
//        model.intro = info;
//        model.coverMiddle = pic;
//        model.tracks = trackNum;
//        model.albumTitle = myTitle;
//        model.albumId = idNum;
        
        [array addObject:idNum];
        
        //        NSLog(@"%@", movie);
    }
    [self.db close];
    return array;
}

#pragma mark 删除数据
-(void)deleteByAlbumId:(NSString *)Id
{
    [self.db open];
    NSString *deleteSql = [NSString stringWithFormat:
                           @"DELETE FROM albums WHERE albumId = '%@'",Id];
    BOOL res = [self.db executeUpdate:deleteSql];
    
    if (res) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
    [self.db close];
}

#pragma mark 删除数据
-(void)deleteByTrackId:(NSInteger)myId
{
    [self.db open];
    NSString *deleteSql = [NSString stringWithFormat:
                           @"DELETE FROM tracks WHERE trackId = '%ld'",myId];
    BOOL res = [self.db executeUpdate:deleteSql];
    
    if (res) {
        NSLog(@"Trackid删除成功");
    } else {
        NSLog(@"Trackid删除失败");
    }
    [self.db close];
}

@end
